# ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上。
from sqlalchemy import Column
from sqlalchemy import String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
# ---------------------------------------定义对象-----------------------------------------------------------------------
Base = declarative_base()  # 创建对象基类


class User(Base):
    __tablename__ = "user"  # 表名称
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    age = Column(String(20))
    # -----------一对多-------------------
    books = relationship("Book")


class Book(Base):
    __tablename__ = "book"
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    user_id = Column(String(20), ForeignKey('user.id'))  # “多”的一方的book表是通过外键关联到user表的


# --------------------------------------初始化数据库连接----------------------------------------------------------------
# create_engine()用来初始化数据库连接。
# SQLAlchemy用一个字符串表示连接信息："数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名"
engine = create_engine("mysql+mysqlconnector://root:root@localhost:3306/yuanzheng")
DBSession = sessionmaker(bind=engine)  # 创建DBSession类型
# --------------------------------------使用ORM-------------------------------------------------------------------------
session = DBSession()  # 创建DBSession对象
# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
# user = session.query(User).filter(User.id == "0001").one()
user = session.query(User).all()
print(type(user))
for u in user:
    print("对象属性:name=%s ,age=%s, bookname=%s" % (u.name, u.age, type(u.books)))
    for b in u.books:
        print(b.name)
    print("对象属性:name=%s ,age=%s, bookname=%s" % (u.name, u.age, u.books))
session.close()
